package com.dins.elashutina.DBHandler;

import com.dins.elashutina.parser.Node;
import com.dins.elashutina.parser.Tree;

import java.sql.*;

/**
 * Created with IntelliJ IDEA.
 * User: ekaterina.lashutina
 * Date: 11.11.13
 * Time: 17:24
 */
public class Filler {
    private final static String CREATE_DATABASE_QUERY = "CREATE DATABASE IF NOT EXISTS `coverage_database`";

    private final static String USE_DB_QUERY = "USE coverage_database";

    private final static String CREATE_B_NODES_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `B_NODES` (" +
            " `B_Node_id` int(11) NOT NULL auto_increment," +
            " `B_Path` varchar(500) NOT NULL," +
            " `B_Parent` varchar(500) NOT NULL," +
            " `B_Component` varchar(500) DEFAULT NULL," +
            " PRIMARY KEY (`B_Node_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_C_NODES_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `C_NODES` (" +
            " `C_Node_id` int(11) NOT NULL auto_increment," +
            " `C_Path` varchar(500)," +
            " `C_Parent` varchar(500) NOT NULL," +
            " `C_Component` varchar(500) DEFAULT NULL," +
            " PRIMARY KEY (`C_Node_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_B_TESTS_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `B_TESTS` (" +
            " `B_Test_id` int(11) NOT NULL auto_increment," +
            " `B_Test_name` varchar(500) NOT NULL," +
            " PRIMARY KEY (`B_Test_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_C_TESTS_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `C_TESTS` (" +
            " `C_Test_id` int(11) NOT NULL auto_increment," +
            " `C_Test_name` varchar(500) NOT NULL," +
            " PRIMARY KEY (`C_Test_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_BUILDS_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `BUILDS` (" +
            " `Build_id` int(11) NOT NULL auto_increment," +
            " `Build_name` varchar(500) NOT NULL," +
            " PRIMARY KEY (`Build_id`)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_B_MAPPING_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `B_MAPPING` (" +
            " `B_Node_id` int(11) NOT NULL," +
            " `Date_time` DATETIME, " +
            " `Build_id` int(11) NOT NULL," +
            " `Conditions_covered, things` double(11,2) NOT NULL," +
            " `Conditions_uncovered, percent` double(11,2) NOT NULL," +
            " `Decisions_covered, things` double(11,2) NOT NULL," +
            " `Decisions_uncovered, percent` double(11,2) NOT NULL," +
            " `B_Test_id` int(11) NOT NULL," +
            "INDEX index_Build_id (Build_id)," +
            "CONSTRAINT FK_Build_id FOREIGN KEY (Build_id) REFERENCES BUILDS (Build_id)," +
            "INDEX index_B_Node_id (B_Node_id)," +
            "CONSTRAINT FK_B_Node_id FOREIGN KEY (B_Node_id) REFERENCES B_NODES (B_Node_id)," +
            "INDEX index_B_Test_id (B_Test_id)," +
            "CONSTRAINT FK_B_Test_id FOREIGN KEY (B_Test_id) REFERENCES B_TESTS (B_Test_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private final static String CREATE_C_MAPPING_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS `C_MAPPING` (" +
            " `C_Node_id` int(11) NOT NULL," +
            " `Date_time` DATETIME, " +
            " `Build_id` int(11) NOT NULL," +
            " `Conditions` double(11,2) NOT NULL," +
            " `Decisions` double(11,2) NOT NULL," +
            " `C_Test_id` int(11) NOT NULL," +
            "INDEX index_Build_id2 (Build_id)," +
            "CONSTRAINT FK_Build_id2 FOREIGN KEY (Build_id) REFERENCES BUILDS (Build_id)," +
            "INDEX index_C_Node_id (C_Node_id)," +
            "CONSTRAINT FK_C_Node_id FOREIGN KEY (C_Node_id) REFERENCES C_NODES (C_Node_id)," +
            "INDEX index_C_Test_id (C_Test_id)," +
            "CONSTRAINT FK_C_Test_id FOREIGN KEY (C_Test_id) REFERENCES C_TESTS (C_Test_id)" +
            ") ENGINE=InnoDB DEFAULT CHARSET=utf8;";

    private static final String DISPLAY_TABLES_QUERY = "SHOW TABLES";


    public static void createTables() {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://coverage-team-db.dins.ru:3306/coverage_database", "coverage-team", "QV4cAsZRl4k3d8uF");
            statement = connection.createStatement();
            statement.executeUpdate("DROP DATABASE IF EXISTS coverage_database");
            statement.executeUpdate(CREATE_DATABASE_QUERY);
            statement.executeUpdate(USE_DB_QUERY);
            statement.executeUpdate(CREATE_B_NODES_TABLE_QUERY);
            statement.executeUpdate(CREATE_C_NODES_TABLE_QUERY);
            statement.executeUpdate(CREATE_B_TESTS_TABLE_QUERY);
            statement.executeUpdate(CREATE_C_TESTS_TABLE_QUERY);
            statement.executeUpdate(CREATE_BUILDS_TABLE_QUERY);
            statement.executeUpdate(CREATE_B_MAPPING_TABLE_QUERY);
            statement.executeUpdate(CREATE_C_MAPPING_TABLE_QUERY);
            System.out.println("Tables successfully created");
            statement.executeQuery(DISPLAY_TABLES_QUERY);

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void addToDB(Tree tree, String buildName, String testName) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection("jdbc:mysql://coverage-team-db.dins.ru:3306/coverage_database", "coverage-team", "QV4cAsZRl4k3d8uF");
            statement = connection.createStatement();
            if (tree.getType() == Tree.Type.BULLSEYE) {
                connection.setAutoCommit(false);
                for (Node node : tree) {
                    int node_id;
                    int build_id;
                    int test_id;
                    String parentName = "NULL";
                    if (node.getParent() != null) {
                        parentName = node.getParent().getName();
                    }

                    ResultSet result = statement.executeQuery("SELECT B_Node_id FROM B_NODES WHERE " +
                            "B_NODES.B_Path=\"" + node.getPath() + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `B_NODES`(`B_Path`,`B_Parent`) VALUES(\"" +
                                node.getPath() + "\",\"" + parentName + "\");");
                        result = statement.executeQuery("SELECT B_Node_id FROM B_NODES WHERE " +
                                "B_NODES.B_Path=\"" + node.getPath() + "\";");
                        result.next();
                    }
                    node_id = result.getInt("B_Node_id");

                    result = statement.executeQuery("SELECT Build_id FROM BUILDS WHERE " +
                            "BUILDS.Build_name=\"" + buildName + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `BUILDS`(`Build_name`) VALUES (\"" +
                                buildName + "\");");
                        result = statement.executeQuery("SELECT Build_id FROM BUILDS WHERE " +
                                "BUILDS.Build_name=\"" + buildName + "\";");
                        result.next();
                    }
                    build_id = result.getInt("Build_id");

                    result = statement.executeQuery("SELECT B_Test_id FROM B_TESTS WHERE " +
                            "B_TESTS.B_Test_name=\"" + testName + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `B_TESTS`(`B_Test_name`) VALUES (\"" +
                                testName + "\");");
                        result = statement.executeQuery("SELECT B_Test_id FROM B_TESTS WHERE " +
                                "B_TESTS.B_Test_name=\"" + testName + "\";");
                        result.next();
                    }
                    test_id = result.getInt("B_Test_id");

                    statement.executeUpdate("INSERT INTO `B_MAPPING`(`B_Node_id`,`Build_id`,`Conditions_covered, things`," +
                            "`Conditions_uncovered, percent`,`Decisions_covered, things`,`Decisions_uncovered, percent`," +
                            "`B_Test_id`) VALUES (" + node_id + "," + build_id + "," + node.getValue(0) + "," +
                            node.getValue(1) + "," + node.getValue(2) + "," + node.getValue(3) + "," + test_id + ");");
                }
                connection.commit();
            } else {
                connection.setAutoCommit(false);
                for (Node node : tree) {
                    int node_id;
                    int build_id;
                    int test_id;
                    String parentName = "NULL";
                    if (node.getParent() != null) {
                        parentName = node.getParent().getName();
                    }

                    ResultSet result = statement.executeQuery("SELECT C_Node_id FROM C_NODES WHERE " +
                            "C_NODES.C_Path=\"" + node.getPath() + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `C_NODES`(`C_Path`,`C_Parent`) VALUES(\"" +
                                node.getPath() + "\",\"" + parentName + "\");");
                        result = statement.executeQuery("SELECT C_Node_id FROM C_NODES WHERE " +
                                "C_NODES.C_Path=\"" + node.getPath() + "\";");
                        result.next();
                    }
                    node_id = result.getInt("C_Node_id");

                    result = statement.executeQuery("SELECT Build_id FROM BUILDS WHERE " +
                            "BUILDS.Build_name=\"" + buildName + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `BUILDS`(`Build_name`) VALUES (\"" +
                                buildName + "\");");
                        result = statement.executeQuery("SELECT Build_id FROM BUILDS WHERE " +
                                "BUILDS.Build_name=\"" + buildName + "\";");
                        result.next();
                    }
                    build_id = result.getInt("Build_id");

                    result = statement.executeQuery("SELECT C_Test_id FROM C_TESTS WHERE " +
                            "C_TESTS.C_Test_name=\"" + testName + "\";");
                    if (!result.next()) {
                        statement.executeUpdate("INSERT INTO `C_TESTS`(`C_Test_name`) VALUES (\"" +
                                testName + "\");");
                        result = statement.executeQuery("SELECT C_Test_id FROM C_TESTS WHERE " +
                                "C_TESTS.C_Test_name=\"" + testName + "\";");
                        result.next();
                    }
                    test_id = result.getInt("C_Test_id");

                    statement.executeUpdate("INSERT INTO `C_MAPPING`(`C_Node_id`,`Build_id`,`Conditions`,`Decisions`," +
                            "`C_Test_id`) VALUES (" + node_id + "," + build_id + "," + node.getValue(0) + "," +
                            node.getValue(1) + "," + test_id + ");");
                }
                connection.commit();
            }
        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException e1) {
                e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
            }

        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

}
